package cn.edu.lcu.cs.javaweb.dao;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.BeanProcessor;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import cn.edu.lcu.cs.javaweb.entity.User;
import cn.edu.lcu.cs.javaweb.util.MyDataSource;

public class UserDaoQueryRunnerImpl implements UserDao {
	// 数据库列名到JavaBean属性名称的映射
//	private final static Map<String, String> COL2PROP = new HashMap<String, String>();

//	static {
	// 如果列名与属性名称相同，可以不放到映射里
//		COL2PROP.put("id", "id");
//		COL2PROP.put("name", "name");
//		COL2PROP.put("password", "password");
//		COL2PROP.put("real_name", "realName");
//	}

	@Override
	public int update(User user) {
		QueryRunner runner = new QueryRunner(MyDataSource.dataSource);
		String sql = "UPDATE `user` set `name`=?, `password`=?, real_name=? where id=?";
		try {
			return runner.update(sql, user.getUsername(), user.getPassword(), user.getRealName(), user.getId());
//			return runner.update(sql, user);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}

	@Override
	public List<User> findAll() {
		QueryRunner runner = new QueryRunner(MyDataSource.dataSource);
		BeanListHandler<User> handler = new BeanListHandler<User>(User.class);
		try {
			return runner.query("select * from user", handler);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public User findById(Long id) {
		QueryRunner runner = new QueryRunner(MyDataSource.dataSource);
		// 普通的RowProcessor，根据同名的原则进行匹配，列的名字与对象属性的名称完全一致
//		BeanHandler<User> handler = new BeanHandler<User>(User.class);
		// GenerousBeanProcessor 会自动把列名单词间的下划线去掉，拼装成属性名，再去对象中查找
		BeanHandler<User> handler = new BeanHandler<User>(User.class, new BasicRowProcessor(new GenerousBeanProcessor()));
		// 利用列名到属性名的映射进行匹配
//		BeanHandler<User> handler = new BeanHandler<User>(User.class, new BasicRowProcessor(new BeanProcessor(COL2PROP)));

		try {
			return runner.query("select * from user where id=?", handler, id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;

	}

	public User save(User user) {
		QueryRunner runner = new QueryRunner(MyDataSource.dataSource);
		String sql = "insert into user(name, password, real_name) values(?, ?, ?)";
		try {
			// insert()返回自动生成的主键。update()虽然可以执行插入语句，但是不会返回主键
			long id = runner.insert(sql, new ScalarHandler<Long>(), user.getUsername(), user.getPassword(),
					user.getRealName());
			user.setId(id);
			return user;
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return null;
	}

	@Override
	public int delete(Long id) {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public long count() {
		QueryRunner runner = new QueryRunner(MyDataSource.dataSource);
		ScalarHandler<Long> handler = new ScalarHandler<Long>();
		try {
			return runner.query("select count(*) from user", handler);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}

	@Override
	public List<User> findByUsername(String username) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public User authenticate(String username, String password) {
		// TODO Auto-generated method stub
		return null;
	}

}
